
/*********************
 **  CreateTime: 2006-7-25 
 **********************/

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using EntLibEC.Cmn;
using EntLibEC.Info.PIM;


namespace EntLibEC.Dac.PIM
{
    /// <summary>
    /// Contains the the data access methods for PropertyValueDA.
    /// </summary>
    public abstract class PropertyValueDA
    {
        #region Sql Script

        private const string SQL_GetPropertyValuesByPropertySysNo
            = @"SELECT a.SysNo,a.PropertySysNo,a.ValueDescription,a.Priority,a.LastEditUserSysNo,a.LastEditTime,b.UserName as LastEditUsername
				FROM PIM_PropertyValue a (NOLOCK)
                LEFT JOIN Ent_Sys_User b (NOLOCK) ON a.LastEditUserSysNo = b.SysNo
                WHERE PropertySysNo = @PropertySysNo
                ORDER BY a.Priority";

        private const string SQL_INSERT_PROPERTYVALUE = "INSERT INTO PIM_PropertyValue(PropertySysNo,ValueDescription,Priority,LastEditUserSysNo,LastEditTime) VALUES (@PropertySysNo,@ValueDescription,@Priority,@LastEditUserSysNo,GETDATE())";
        private const string SQL_UPDATE_PROPERTYVALUE = "UPDATE PIM_PropertyValue SET PropertySysNo=@PropertySysNo,ValueDescription=@ValueDescription,Priority=@Priority,LastEditUserSysNo=@LastEditUserSysNo,LastEditTime=GETDATE() WHERE SysNo=@SysNo";
        private const string SQL_DELETE_PROPERTYVALUE = "DELETE FROM PIM_PropertyValue WHERE SysNo = @SysNo";
        private const string SQL_PROPERTYVALUE_EXIST = "SELECT SysNo FROM PIM_PropertyValue (NOLOCK) WHERE PropertySysNo=@PropertySysNo AND ValueDescription=@ValueDescription AND SysNo<>@SysNo";
        private const string SQL_PROPERTY_EXIST = "SELECT SysNo FROM PIM_PropertyValue (NOLOCK) WHERE PropertySysNo=@PropertySysNo";
        private const string SQL_PRIORITY_EXIST = "SELECT SysNo FROM PIM_PropertyValue (NOLOCK) WHERE PropertySysNo=@PropertySysNo AND Priority=@Priority AND SysNo<>@SysNo";
        #endregion

        public static PropertyValueInfo Map(DataRow row)
        {
            PropertyValueInfo info = new PropertyValueInfo();

            info.SysNo = Convert.ToInt32(row["SysNo"]);
            info.PropertySysNo = Convert.ToInt32(row["PropertySysNo"]);
            info.ValueDescription = row["ValueDescription"].ToString();
            info.Priority = Convert.ToInt32(row["Priority"]);
            info.LastEditUserSysNo = Convert.ToInt32(row["LastEditUserSysNo"]);
            info.LastEditTime = Convert.ToDateTime(row["LastEditTime"]);
            info.LastEditUsername = row["LastEditUsername"].ToString();
            return info;
        }

        public static List<PropertyValueInfo> GetPropertyValues(int sysNo)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@PropertySysNo", SqlDbType.Int)
            };
            parms[0].Value = sysNo;

            DataTable dt = DataAccessFacade.GetDataTable(SQL_GetPropertyValuesByPropertySysNo, parms);
            if (dt == null)
                return null;

            List<PropertyValueInfo> list = new List<PropertyValueInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(Map(row));
            }
            return list;
        }

        public static int InsertPropertyValue(PropertyValueInfo info)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@SysNo", SqlDbType.Int),
                new SqlParameter("@PropertySysNo", SqlDbType.Int),
                new SqlParameter("@ValueDescription", SqlDbType.NVarChar,100),
                new SqlParameter("@Priority", SqlDbType.Int),
                new SqlParameter("@LastEditUserSysNo", SqlDbType.Int)
            };

            parms[0].Value = info.SysNo;
            parms[1].Value = info.PropertySysNo;
            parms[2].Value = info.ValueDescription;
            parms[3].Value = info.Priority;
            parms[4].Value = info.LastEditUserSysNo;

            object obj = DataAccessFacade.ExecSqlCommand(SQL_INSERT_PROPERTYVALUE, parms);
            return Convert.ToInt32(obj);
        }

        public static int UpdatePropertyValue(PropertyValueInfo info)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@SysNo", SqlDbType.Int),
                new SqlParameter("@PropertySysNo", SqlDbType.Int),
                new SqlParameter("@ValueDescription", SqlDbType.NVarChar, 100),
                new SqlParameter("@Priority", SqlDbType.Int),
                new SqlParameter("@LastEditUserSysNo", SqlDbType.Int)
            };

            parms[0].Value = info.SysNo;
            parms[1].Value = info.PropertySysNo;
            parms[2].Value = info.ValueDescription;
            parms[3].Value = info.Priority;
            parms[4].Value = info.LastEditUserSysNo;

            object obj = DataAccessFacade.ExecSqlCommand(SQL_UPDATE_PROPERTYVALUE, parms);
            return Convert.ToInt32(obj);
        }

        public static void DeletePropertyValue(int SysNo)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@SysNo", SqlDbType.Int)
            };
            parms[0].Value = SysNo;
            DataAccessFacade.ExecSqlCommand(SQL_DELETE_PROPERTYVALUE, parms);
        }
        public static bool PropertyValueExist(PropertyValueInfo info)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@SysNo", SqlDbType.Int),
                new SqlParameter("@PropertySysNo", SqlDbType.Int),
                new SqlParameter("@ValueDescription", SqlDbType.NVarChar, 100)
            };
            

            parms[0].Value = info.SysNo;
            parms[1].Value = info.PropertySysNo;
            parms[2].Value = info.ValueDescription;


            object obj = DataAccessFacade.ExecScalarSqlCommand(SQL_PROPERTYVALUE_EXIST, parms);
            if (obj == null)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        public static bool PriorityExist(PropertyValueInfo info)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@SysNo", SqlDbType.Int),
                new SqlParameter("@PropertySysNo", SqlDbType.Int),
                new SqlParameter("@Priority", SqlDbType.NVarChar, 100)
            };


            parms[0].Value = info.SysNo;
            parms[1].Value = info.PropertySysNo;
            parms[2].Value = info.Priority;


            object obj = DataAccessFacade.ExecScalarSqlCommand(SQL_PRIORITY_EXIST, parms);
            if (obj == null)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        public static bool PropertyExist(int sysNo)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@PropertySysNo", SqlDbType.Int)
            };
            parms[0].Value = sysNo;
            object obj = DataAccessFacade.ExecScalarSqlCommand(SQL_PROPERTY_EXIST, parms);
            if (obj == null)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        public static DataTable SearchPropertyValue(PropertyValueSearchCondition condition)
        {
            StringBuilder sqlStr = new StringBuilder(@"SELECT a.SysNo,a.PropertySysNo,a.ValueDescription,a.Priority,a.LastEditUserSysNo,a.LastEditTime,b.UserName as LastEditUsername
				FROM PIM_PropertyValue a (NOLOCK)
                LEFT JOIN Ent_Sys_User b (NOLOCK) ON a.LastEditUserSysNo = b.SysNo
                WHERE 1=1");
            sqlStr.Append(" AND a.PropertySysNo ='" + condition.PropertySysNo+ "'");
            if (condition.Description != string.Empty)
            {
                sqlStr.Append(" AND a.ValueDescription Like " + EntLibEC.Cmn.Util.ToSqlLikeString(condition.Description));
            }
            if (condition.EditorUserId != 0)
            {
                sqlStr.Append(" AND a.LastEditUserSysNo='" + condition.EditorUserId + "'");
            }
            sqlStr.Append("ORDER BY a.Priority");
            return DataAccessFacade.GetDataTable(sqlStr.ToString());
        }
    }
}